﻿using System;
using System.Data;
using System.Globalization;
using System.Text.RegularExpressions;
using DAO;
using System.IO;
using System.Data.SqlClient;

namespace BUS
{
    public class CommonControl
    {
        readonly Connection _conn;
        static string sErr = "";
        private SqlDataAdapter adapter;
        public CommonControl()
        {
            _conn = new Connection();
        }
        public DateTime getDateSys()
        {
            return _conn.GetDateSys();
        }
        public static int ConvertDateToInt(DateTime dt)
        {
            if (dt == null || dt.Year == 1)
            {
                return 19000101;
            }
            return dt.Year * 10000 + dt.Month * 100 + dt.Day;
        }
        public static string GetParseExpressionDate(string columnDate, string dtb)
        {
            DBAControl dba_ctr = new DBAControl();
            DTO.DBAInfo dba_info = dba_ctr.Get(dtb, ref sErr);
            string result = String.Format("SUBSTRING(CONVERT({0},System.String),7,2)+'/'+SUBSTRING(CONVERT({0},System.String),5,2)+'/'+SUBSTRING(CONVERT({0},System.String),1,4)", columnDate);
            string fm = dba_info.DATE_FORMAT;  // lay tu database

            switch (fm)
            {
                case "A":
                    result = String.Format("SUBSTRING(CONVERT({0},System.String),5,2)+'/'+SUBSTRING(CONVERT({0},System.String),7,2)+'/'+SUBSTRING(CONVERT({0},System.String),1,4)", columnDate);
                    break;
                case "B":
                    result = String.Format("SUBSTRING(CONVERT({0},System.String),7,2)+'/'+SUBSTRING(CONVERT({0},System.String),5,2)+'/'+SUBSTRING(CONVERT({0},System.String),1,4)", columnDate);
                    break;
                case "C":
                    result = String.Format("SUBSTRING(CONVERT({0},System.String),1,4)+'/'+SUBSTRING(CONVERT({0},System.String),5,2)+'/'+SUBSTRING(CONVERT({0},System.String),7,2)", columnDate);
                    break;
            }
            return result;
        }
        public static string GetParseExpressionPeriod(string columnDate)
        {
            string result = String.Format("SUBSTRING(CONVERT({0},System.String),5,3)+'/'+SUBSTRING(CONVERT({0},System.String),1,4)", columnDate);
            return result;
        }

        public static DateTime? ConvertIntToDate(int dt)
        {
            if (dt == 0 || dt == -1 || dt == 19000101)
            {
                return new DateTime(1900, 1, 1);
            }
            DateTime? result = null;
            try
            {
                //result = new DateTime(
                result = new DateTime(dt / 10000, (dt % 10000) / 100, ((dt % 10000) % 100));
            }
            catch { }
            return result;
        }
        public static String ConvertIntToString(int dt)
        {
            if (dt == 0 || dt == -1 || dt == 19000101)
            {
                return "";
            }
            String mon = Convert.ToString((dt % 10000) / 100);
            if (mon.Length == 1) mon = "0" + mon;
            String dd = Convert.ToString((dt % 10000) % 100);
            if (dd.Length == 1) dd = "0" + dd;
            return (String.Format("{0}/{1}/{2}", dt / 10000, mon, dd));
        }

        public static String ConvertIntToDateString(int dt, string dtb)
        {
            if (dt == 0 || dt == -1 || dt == 19000101)
            {
                return "";
            }
            DateTime? result = null;
            try
            {
                //result = new DateTime(
                result = new DateTime(dt / 10000, (dt % 10000) / 100, ((dt % 10000) % 100));
                string kq = Get_Date_Format(dtb);
                string day = "00" + result.Value.Day;
                day = day.Substring(day.Length - 2);
                string month = "00" + result.Value.Month;
                month = month.Substring(month.Length - 2);
                string year = "0000" + result.Value.Year;
                year = year.Substring(year.Length - 4);
                kq = kq.Replace("dd", day).Replace("MM", month).Replace("yyyy", year);
                return kq;// result.Value.ToString(Get_Date_Format(dtb));
            }
            catch { }
            return "";
        }
        public DataTable SelectData(string fields, string table, ref string sErr)
        {
            return _conn.SelectData(fields, table, ref sErr);
        }
        public static String Get_Date_Format(String dtb)
        {
            string kq = "";
            string sErr = "";
            DBAControl dba_ctr = new DBAControl();
            DTO.DBAInfo dba_info = dba_ctr.Get(dtb, ref sErr);

            string fm = dba_info.DATE_FORMAT;  // lay tu database

            switch (fm)
            {
                case "A":
                    kq = "MM/dd/yyyy";
                    break;
                case "B":
                    kq = "dd/MM/yyyy";
                    break;
                case "C":
                    kq = "yyyy/MM/dd";
                    break;
            }

            return kq;

        }

        public static String Format_Number(Decimal temp, string dtb)
        {
            // lay tu database
            String kq = "";
            string sErr = "";

            DBAControl dba_ctr = new DBAControl();
            DTO.DBAInfo dba_info = dba_ctr.Get(dtb, ref sErr);

            string dec = dba_info.DECIMAL_SEPERATOR;
            string thou = dba_info.THOUSAND_SEPERATOR;
            string fm = thou + dec;
            //   string chuoi = new string('0',Convert.ToInt32(dba_info.DECIMAL_PLACES_SUNBUSINESS));
            string num_dec = "N" + dba_info.DECIMAL_PLACES_SUNBUSINESS;
            switch (fm)
            {
                case ".,":
                    CultureInfo a = new CultureInfo("de-DE");
                    kq = temp.ToString(num_dec, a);
                    break;
                case ",.":
                    CultureInfo b = new CultureInfo("en-US");
                    kq = temp.ToString(num_dec, b);
                    break;
            }

            return kq;
        }
        public static String Format_NumberAcc(Decimal temp, string dtb)
        {
            // lay tu database
            String kq = "";
            string sErr = "";

            DBAControl dba_ctr = new DBAControl();
            DTO.DBAInfo dba_info = dba_ctr.Get(dtb, ref sErr);

            string dec = dba_info.DECIMAL_SEPERATOR;
            string thou = dba_info.THOUSAND_SEPERATOR;
            string fm = thou + dec;
            //   string chuoi = new string('0',Convert.ToInt32(dba_info.DECIMAL_PLACES_SUNBUSINESS));
            string num_dec = "N" + dba_info.DECIMAL_PLACES_SUNACCOUNT;
            switch (fm)
            {
                case ".,":
                    CultureInfo a = new CultureInfo("de-DE");
                    kq = temp.ToString(num_dec, a);
                    break;
                case ",.":
                    CultureInfo b = new CultureInfo("en-US");
                    kq = temp.ToString(num_dec, b);
                    break;
            }

            return kq;
        }
        public static string GetCultureString(string dtb)
        {
            string sErr = "";

            DBAControl dba_ctr = new DBAControl();
            DTO.DBAInfo dba_info = dba_ctr.Get(dtb, ref sErr);

            string dec = dba_info.DECIMAL_SEPERATOR;
            string thou = dba_info.THOUSAND_SEPERATOR;
            string fm = thou + dec;
            if (fm == ".,")
                return "de-DE";
            else if (fm == ",.")
                return "en-US";
            return "";

        }
        public static String GetNumberFormat(string dtb)
        {
            string sErr = "";
            String kq = "";
            DBAControl dba_ctr = new DBAControl();
            DTO.DBAInfo dba_info = dba_ctr.Get(dtb, ref sErr);
            string dec = dba_info.DECIMAL_SEPERATOR;
            string thou = dba_info.THOUSAND_SEPERATOR;
            int icountdec = dba_info.DECIMAL_PLACES_SUNBUSINESS == "" ? 0 : Convert.ToInt32(dba_info.DECIMAL_PLACES_SUNBUSINESS);
            // kq = "###" + thou + "###" + dec + new String('#', icountdec) + "" + currency + "";
            kq = "N" + icountdec;
            return kq;
        }
        public static String GetNumberFormatA(string dtb)
        {
            string sErr = "";
            String kq = "";
            DBAControl dba_ctr = new DBAControl();
            DTO.DBAInfo dba_info = dba_ctr.Get(dtb, ref sErr);
            string dec = dba_info.DECIMAL_SEPERATOR;
            string thou = dba_info.THOUSAND_SEPERATOR;
            int icountdec = dba_info.DECIMAL_PLACES_SUNACCOUNT == "" ? 0 : Convert.ToInt32(dba_info.DECIMAL_PLACES_SUNACCOUNT);
            // kq = "###" + thou + "###" + dec + new String('#', icountdec) + "" + currency + "";
            kq = "N" + icountdec;
            return kq;
        }
        public static Decimal Format_StringToNumber(string temp, string dtb)
        {
            // lay tu database Regex.Matches(\d 
            Decimal kq = 0;
            if (Regex.IsMatch(temp, @"\d+"))
            {


                string sErr = "";

                DBAControl dba_ctr = new DBAControl();
                DTO.DBAInfo dba_info = dba_ctr.Get(dtb, ref sErr);

                string dec = dba_info.DECIMAL_SEPERATOR;
                string thou = dba_info.THOUSAND_SEPERATOR;
                string fm = thou + dec;
                //   string chuoi = new string('0',Convert.ToInt32(dba_info.DECIMAL_PLACES_SUNBUSINESS));
                //      string num_dec = "N" + dba_info.DECIMAL_PLACES_SUNBUSINESS;
                switch (fm)
                {
                    case ".,":
                        CultureInfo a = new CultureInfo("de-DE");
                        kq = Convert.ToDecimal(temp, a);
                        break;
                    case ",.":
                        CultureInfo b = new CultureInfo("en-US");
                        kq = Convert.ToDecimal(temp, b);
                        break;
                }
            }
            return kq;
        }

        public static Decimal Round(Decimal obj, String dtb)
        {
            DBAControl control = new DBAControl();
            DTO.DBAInfo dba_info = control.Get(dtb, ref sErr);
            obj = Decimal.Round(obj, Convert.ToInt32(dba_info.DECIMAL_PLACES_SUNBUSINESS));
            return obj;
        }

        public static String GetPeriodString(int p)
        {
            //Regex.IsMatch(p, @"^[0-9]{2}[1-9]{1}/[1-9]{1}[0-9]{3}$")

            if (p.ToString().Length == 7)
            {
                string result = p.ToString();
                return String.Format("{0}/{1}", result.Substring(4, 3), result.Substring(0, 4));
            }
            return "";
        }

        public static int ConvertStringToPeriod(string p)
        {
            if (Regex.IsMatch(p, "^[0-9]{3}/[1-9]{1}[0-9]{3}$"))
            {
                string period = (p.Split('/'))[1] + (p.Split('/'))[0];
                return Convert.ToInt32(period);
            }
            return 1900001;
        }

        public static int ConvertDateToPeriodInt(DateTime? dateTime)
        {
            if (dateTime == null)
                return 1900001;
            return dateTime.Value.Year * 1000 + dateTime.Value.Month;
        }
        public IDataReader executeQuery(string sqlString)
        {
            _conn.openConnection();
            IDataReader kq = _conn.executeQuery(sqlString);
            _conn.closeConnection();
            return kq;
        }
        public DataTable executeSelectQuery(string sqlString)
        {
            return _conn.executeSelectQuery(sqlString);
        }

        public void executeNonQuery(string sqlString)
        {
            _conn.openConnection();
            _conn.executeNonQuery(sqlString);
            _conn.closeConnection();
        }

        public object executeScalar(string sqlString)
        {
            _conn.openConnection();
            object kq = _conn.executeScalar(sqlString);
            _conn.closeConnection();
            return kq;
        }

        public static object GetTableStruct(string tableName)
        {
            object dt = null;
            switch (tableName)
            {
                #region Common

                case "Vendor": dt = DTO.VENDORInfo.ToDataTable(); break;
                case "Budget": dt = DTO.BudgetsInfo.ToDataTable(); break;
                case "Budget1": dt = DTO.BudgetsInfo.ToDataTable(); break;
                case "DB": dt = DTO.DBAInfo.ToDataTable(); break;
                case "POD": dt = DTO.PODInfo.ToDataTable(); break;
                case "POG": dt = DTO.POGInfo.ToDataTable(); break;
                //case "POP": dt = DTO.POPInfo.ToDataTable(); break;
                case "SNS": dt = DTO.SNSInfo.ToDataTable(); break;
                case "DA": dt = DTO.LIST_DAInfo.ToDataTable(); break;
                case "MLC": dt = DTO.MLCInfo.ToDataTable(); break;
                case "MMT": dt = DTO.TVC_MVPInfo.ToDataTable(); break;
                case "ItemRecord": dt = DTO.TVC_RFITMInfo.ToDataTable(); break;
                case "Location": dt = DTO.TVC_LOOKUPInfo.ToDataTable(); break;
                case "BinNo": dt = DTO.TVC_LOOKUPInfo.ToDataTable(); break;
                case "Supplier": dt = DTO.TVC_LOOKUPInfo.ToDataTable(); break;
                case "Categories": dt = DTO.TVC_LOOKUPInfo.ToDataTable(); break;
                case "LOOKUP_ACCNT": dt = DTO.TVC_LOOKUPInfo.ToDataTable(); break;
                case "UND": dt = DTO.TVC_LOOKUPInfo.ToDataTable(); break;
                case "UCT": dt = DTO.TVC_LOOKUPInfo.ToDataTable(); break;
                case "Parameter": dt = DTO.TVC_LOOKUPInfo.ToDataTable(); break;
                case "RUS": dt = DTO.RuleProfileInfo.ToDataTable(); break;
                case "MMEN": DTO.TVC_MFMOVInfo inf = new DTO.TVC_MFMOVInfo(); dt = inf.ToDataTable(); break;
                #endregion

                #region Sale
                case "SAL":
                #endregion

                #region Sample

                case "PLS":
                    break;
                case "BUG":
                    break;
                #endregion

                #region Activity
                #endregion
                //case "MMS": BUS.MMS_MEMBERControl ctrMMS = new MMS_MEMBERControl(); dt = ctrMMS.ToTransferInStruct(); break;
                //case "TBD": BUS.TBDControl ctrTBD = new TBDControl(); dt = ctrTBD.ToTransferInStruct(); break;
                //case "TIM": BUS.TIMControl ctrTIM = new TIMControl(); dt = ctrTIM.ToTransferInStruct(); break;
                //case "ZMS": BUS.ZMSControl ctrZMS = new ZMSControl(); dt = ctrZMS.ToTransferInStruct(); break;
                #region Incentive
                #endregion
                case "RSA":
                case "RQM":
                case "RQY": BUS.LIST_QDControl ctrZMS = new LIST_QDControl(); dt = ctrZMS.ToTransferInStruct(); break;
                case "RTD": BUS.RTDControl ctrRTD = new RTDControl(); dt = ctrRTD.ToTransferInStruct(); break;
                case "RPT":
                case "COR":
                case "DIS":
                case "TOW":
                case "LOK":
                case "TG1":
                case "TG2":
                case "TG3":
                case "TG4": BUS.LIST_LOOKUPControl ctrTG4 = new LIST_LOOKUPControl(); dt = ctrTG4.ToTransferInStruct(); break;
                case "QDADD": BUS.LIST_QD_SCHEMAControl ctrsChema = new LIST_QD_SCHEMAControl(); dt = ctrsChema.ToTransferInStruct(); break;
                case "DPT": dt = DTO.DepartmentsInfo.ToDataTable(); break;

            }
            if (dt is DataTable) ((DataTable)dt).TableName = tableName;
            return dt;
        }
        public static Object GetAllDataTable(string tableName, string dtb, string from, string to, ref string sErr)
        {
            switch (tableName)
            {
                //case "AND": BUS.ANDControl ctrAND = new ANDControl(); return ctrAND.GetTransferOut(dtb, from, to, ref sErr); break;
                //case "ANV": BUS.SSRFANVControl ctrANV = new SSRFANVControl(); return ctrANV.GetTransferOut(dtb, from, to, ref sErr); break;
                //case "APP":
                //    BUS.OBJ_APPHDRControl ctrAPP = new OBJ_APPHDRControl();
                //    DataTable dtAPP = ctrAPP.GetTransferOut(dtb, from, to, ref sErr);
                //    dtAPP.TableName = "APPHDR";
                //    BUS.OBJ_APPDETControl detailCtr = new OBJ_APPDETControl();
                //    DataTable dtDET = detailCtr.GetTransferOut(dtb, from, to, ref sErr);
                //    dtDET.TableName = "APPDEF";
                //    BUS.MMS_MEMAPPControl memCtr = new MMS_MEMAPPControl();
                //    DataTable dtMMS = memCtr.GetTransferOut(dtb, from, to, ref sErr);
                //    dtMMS.TableName = "APPMEM";

                //    DataSet dset = new DataSet();
                //    dset.Tables.Add(dtAPP.Copy());
                //    dset.Tables.Add(dtDET.Copy());
                //    dset.Tables.Add(dtMMS.Copy());
                //    return dset;
                //case "LOG": BUS.OBJ_LOGControl ctrLOG = new OBJ_LOGControl(); return ctrLOG.GetTransferOut(dtb, from, to, ref sErr); break;
                //case "APT": BUS.APTControl ctrAPT = new APTControl(); return ctrAPT.GetTransferOut(dtb, from, to, ref sErr); break;
                //case "CAD": BUS.CADControl ctrCAD = new CADControl(); return ctrCAD.GetTransferOut(dtb, from, to, ref sErr); break;
                //case "Vendor": BUS.VENDORControl ctrVendor = new VENDORControl(); return ctrVendor.GetTransferOut(dtb, from, to, ref sErr); break;
                case "CND": BUS.SSRFCNDControl ctrCND = new SSRFCNDControl(); return ctrCND.GetTransferOut(dtb, from, to, ref sErr); break;
                case "CNV": BUS.SSRFCNVControl ctrCNV = new SSRFCNVControl(); return ctrCNV.GetTransferOut(dtb, from, to, ref sErr); break;
                case "CVD": BUS.SSRFCVDControl ctrCVD = new SSRFCVDControl(); return ctrCVD.GetTransferOut(dtb, from, to, ref sErr); break;
                case "DB": BUS.DBAControl ctrDB = new DBAControl(); return ctrDB.GetTransferOut(dtb, from, to, ref sErr); break;
                case "POD": BUS.PODControl ctrPOD = new PODControl(); return ctrPOD.GetTransferOut(dtb, from, to, ref sErr); break;
                case "POG": BUS.POGControl ctrPOG = new POGControl(); return ctrPOG.GetTransferOut(dtb, from, to, ref sErr); break;
                case "POP": BUS.POPControl ctrPOP = new POPControl(); return ctrPOP.GetTransferOut(dtb, from, to, ref sErr); break;
                //case "POS": BUS.POSControl ctrPOS = new POSControl(); return ctrPOS.GetTransferOut(dtb, from, to, ref sErr); break;
                //case "PRD": BUS.PRDControl ctrPRD = new PRDControl(); return ctrPRD.GetTransferOut(dtb, from, to, ref sErr); break;
                case "PRB": BUS.LIST_PRIControl ctrPRB = new LIST_PRIControl(); return ctrPRB.GetTransferOut(dtb, from, to, ref sErr); break;
                //case "PRO": BUS.MMS_PROGRAMMEControl ctrPRO = new MMS_PROGRAMMEControl(); return ctrPRO.GetTransferOut(dtb, from, to, ref sErr); break;

                //case "MMS": BUS.MMS_MEMBERControl ctrMMS = new MMS_MEMBERControl(); return ctrMMS.GetTransferOut(dtb, from, to, ref sErr); break;
                //case "TBD": BUS.TBDControl ctrTBD = new TBDControl(); return ctrTBD.GetTransferOut(dtb, from, to, ref sErr); break;
                //case "TIM": BUS.TIMControl ctrTIM = new TIMControl(); return ctrTIM.GetTransferOut(dtb, from, to, ref sErr); break;
                case "RSA":
                case "RQM":
                case "RQY": BUS.LIST_QDControl ctrZMS = new LIST_QDControl(); return ctrZMS.GetTransferOut_LIST_QD(dtb, ref sErr); break;
                case "SNS": BUS.SNSControl ctrSNS = new SNSControl(); return ctrSNS.GetTransferOut(dtb, from, to, ref sErr); break;
                case "RTD": BUS.RTDControl ctrRTD = new RTDControl(); return ctrRTD.GetTransferOut(dtb, from, to, ref sErr); break;
                case "RPT":
                case "COR":
                case "PRG":
                case "DIS":
                case "LOK":
                case "TOW":
                case "SAL":
                case "TG1":
                case "TG2":
                case "TG3":
                case "TG4": BUS.LIST_LOOKUPControl ctrLOO = new LIST_LOOKUPControl(); return ctrLOO.GetTransferOut(dtb, from, to, ref sErr); break;
                //case "TDS": BUS.TDSControl ctrTDS = new TDSControl(); return ctrTDS.GetTransferOut(dtb, from, to, ref sErr);
                case "QDADD": BUS.LIST_QD_SCHEMAControl ctrSchema = new LIST_QD_SCHEMAControl(); return ctrSchema.GetTransferOut(dtb, from, to, ref sErr);
            }

            return null;
        }

        public static string TransferInData(DataRow row, string tableName, DTO.PODInfo PODInfo)
        {
            string SUNDB = PODInfo.Database;
            try
            {
                switch (tableName)
                {
                    #region Common


                    case "Vendor": BUS.VENDORControl ctrVendor = new VENDORControl(); return ctrVendor.TransferIn(row);
                    case "Budget":
                        BUS.BudgetsControl ctrBudget = new BudgetsControl();
                        if (row["BudgetCode"].ToString() == "")
                            return "Could not save blank Budget, please check!";
                        if (ctrBudget.IsExist_GroupOtherShared(row["BudgetCode"].ToString(), int.Parse(row["Year"].ToString()), row["Group"].ToString(), row["isShared"] + "" == "" ? (Int16)0 : (Int16)row["isShared"]))
                            return "There are shared budget and none shared budget in budget group " + row["Group"].ToString() + ", please check!";

                        sErr = ctrBudget.TransferIn(row);
                        sErr += ctrBudget.Update_Shared(new DTO.BudgetsInfo(row));
                        return sErr;

                    case "Budget1":
                        BUS.BudgetsControl ctrBudget1 = new BudgetsControl();
                        if (row["BudgetCode"].ToString() == "")
                            return "Could not save blank Budget, please check!";
                        if (ctrBudget1.IsExist_GroupOtherShared1(row["BudgetCode"].ToString(), int.Parse(row["Year"].ToString()), row["Group"].ToString(), row["isShared"] + "" == "" ? (Int16)0 : (Int16)row["isShared"]))
                            return "There are shared budget and none shared budget in budget group " + row["Group"].ToString() + ", please check!";
                        sErr = ctrBudget1.TransferIn1(row);
                        sErr += ctrBudget1.Update_Shared(new DTO.BudgetsInfo(row));
                        return sErr;
                    case "ItemRecord": BUS.TVC_RFITMControl ctrRFITM = new TVC_RFITMControl(PODInfo.Business_ID);

                        return ctrRFITM.TransferIn(SUNDB, row);

                    case "MMT": BUS.TVC_MVPControl ctrMMT = new TVC_MVPControl(PODInfo.Business_ID); return ctrMMT.TransferIn(PODInfo.Database, row);
                    case "Location":
                    case "BinNo":
                    case "Supplier":
                    case "Categories":
                    case "LOOKUP_ACCNT":
                    case "UND":
                    case "UCT":
                    case "Parameter": BUS.TVC_LOOKUPControl ctrParameter = new TVC_LOOKUPControl(PODInfo.Business_ID); return ctrParameter.TransferIn(PODInfo.Database, row);
                    case "RUS": BUS.RuleProfileControl ctrRUS = new RuleProfileControl(PODInfo.Business_ID); return ctrRUS.TransferIn(PODInfo.Database, row);
                    case "DB": BUS.DBAControl ctrDB = new DBAControl(); return ctrDB.TransferIn(row);
                    case "POD": BUS.PODControl ctrPOD = new PODControl(); return ctrPOD.TransferIn(row);
                    case "POG": BUS.POGControl ctrPOG = new POGControl(); return ctrPOG.TransferIn(row);
                    case "POP": BUS.POPControl ctrPOP = new POPControl(); return ctrPOP.TransferIn(row);
                    case "SNS": BUS.SNSControl ctrSNS = new SNSControl(); return ctrSNS.TransferIn(row);
                    case "DA": BUS.LIST_DAControl ctrDA = new LIST_DAControl(); return ctrDA.TransferIn(row);
                    case "MLC": BUS.MLCControl ctrMLC = new MLCControl(); return ctrMLC.TransferIn(row);
                    #endregion

                    #region Reference
                    #endregion

                    #region Sale
                    case "SAL":
                    #endregion

                    #region Activity
                    #endregion

                    #region Incentive
                    #endregion

                    //case "POS": BUS.POSControl ctrPOS = new POSControl(); return ctrPOS.TransferIn(row);
                    case "RSA":
                    case "RQM":
                    case "RQY": BUS.LIST_QDControl ctrPPV = new LIST_QDControl(); ctrPPV.TransferIn(row, ref sErr); return sErr;
                    //case "PRD": BUS.PRDControl ctrPRD = new PRDControl(); return ctrPRD.TransferIn(row);
                    case "PRB": BUS.LIST_PRIControl ctrPRB = new LIST_PRIControl(); return ctrPRB.TransferIn(row);
                    //case "PRO": BUS.MMS_PROGRAMMEControl ctrPRO = new MMS_PROGRAMMEControl(); return ctrPRO.TransferIn(row);
                    //case "MMS": BUS.MMS_MEMBERControl ctrMMS = new MMS_MEMBERControl(); return ctrMMS.TransferIn(row);
                    //case "TBD": BUS.TBDControl ctrTBD = new TBDControl(); return ctrTBD.TransferIn(row);
                    //case "TIM": BUS.TIMControl ctrTIM = new TIMControl(); return ctrTIM.TransferIn(row);
                    //case "ZMS": BUS.ZMSControl ctrZMS = new ZMSControl(); return ctrZMS.TransferIn(row);

                    case "RTD": BUS.RTDControl ctrRTD = new RTDControl(); return ctrRTD.TransferIn(row);
                    case "RPT":
                    case "COR":
                    case "DIS":
                    case "TOW":
                    case "TG1":
                    case "TG2":
                    case "LOK":
                    case "TG3":
                    case "TG4": BUS.LIST_LOOKUPControl ctrTG4 = new LIST_LOOKUPControl(); return ctrTG4.TransferIn(row);
                    //case "TDS": BUS.TDSControl ctrTDS = new TDSControl(); return ctrTDS.TransferIn(row);
                    case "QDADD": BUS.LIST_QD_SCHEMAControl ctrSchema = new LIST_QD_SCHEMAControl(); return ctrSchema.TransferIn(row);
                    case "DPT": BUS.DepartmentsControl DPTCtr = new DepartmentsControl(); return DPTCtr.TransferIn(row);

                }
                return "";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        //public static string TransferInData(DataRow row, string tableName)
        //{
        //    try
        //    {
        //        switch (tableName)
        //        {
        //            #region Common


        //            case "Vendor": BUS.VENDORControl ctrVendor = new VENDORControl(); return ctrVendor.TransferIn(row);
        //            case "Budget": 
        //                BUS.BudgetsControl ctrBudget = new BudgetsControl(); 
        //                if (row["BudgetCode"].ToString() == "")
        //                    return "Could not save blank Budget, please check!";
        //                if (ctrBudget.IsExist_GroupOtherShared(row["BudgetCode"].ToString(), int.Parse(row["Year"].ToString()), row["Group"].ToString(), row["isShared"]+"" == "" ? (Int16)0 : (Int16)row["isShared"]))
        //                    return "There are shared budget and none shared budget in budget group " + row["Group"].ToString() + ", please check!";

        //                sErr = ctrBudget.TransferIn(row);
        //                sErr += ctrBudget.Update_Shared(new DTO.BudgetsInfo(row));
        //                return sErr;

        //            case "Budget1": 
        //                BUS.BudgetsControl ctrBudget1 = new BudgetsControl(); 
        //                if (row["BudgetCode"].ToString() == "")
        //                    return "Could not save blank Budget, please check!";
        //                if (ctrBudget1.IsExist_GroupOtherShared1(row["BudgetCode"].ToString(), int.Parse(row["Year"].ToString()), row["Group"].ToString(), row["isShared"]+"" == "" ? (Int16)0 : (Int16)row["isShared"]))
        //                    return "There are shared budget and none shared budget in budget group " + row["Group"].ToString() + ", please check!";
        //                sErr = ctrBudget1.TransferIn1(row);
        //                sErr += ctrBudget1.Update_Shared(new DTO.BudgetsInfo(row));
        //                return sErr;
        //            case "ItemRecord": BUS.TVC_RFITMControl ctrRFITM = new TVC_RFITMControl(); return ctrRFITM.TransferIn(row);
        //            case "DB": BUS.DBAControl ctrDB = new DBAControl(); return ctrDB.TransferIn(row);
        //            case "POD": BUS.PODControl ctrPOD = new PODControl(); return ctrPOD.TransferIn(row);
        //            case "POG": BUS.POGControl ctrPOG = new POGControl(); return ctrPOG.TransferIn(row);
        //            case "POP": BUS.POPControl ctrPOP = new POPControl(); return ctrPOP.TransferIn(row);
        //            case "SNS": BUS.SNSControl ctrSNS = new SNSControl(); return ctrSNS.TransferIn(row);
        //            case "DA": BUS.LIST_DAControl ctrDA = new LIST_DAControl(); return ctrDA.TransferIn(row);
        //            case "MLC": BUS.MLCControl ctrMLC = new MLCControl(); return ctrMLC.TransferIn(row);
        //            #endregion

        //            #region Reference
        //            #endregion

        //            #region Sale
        //            case "SAL":
        //            #endregion

        //            #region Activity
        //            #endregion

        //            #region Incentive
        //            #endregion

        //            //case "POS": BUS.POSControl ctrPOS = new POSControl(); return ctrPOS.TransferIn(row);
        //            case "RSA":
        //            case "RQM":
        //            case "RQY": BUS.LIST_QDControl ctrPPV = new LIST_QDControl(); ctrPPV.TransferIn(row, ref sErr); return sErr;
        //            //case "PRD": BUS.PRDControl ctrPRD = new PRDControl(); return ctrPRD.TransferIn(row);
        //            case "PRB": BUS.LIST_PRIControl ctrPRB = new LIST_PRIControl(); return ctrPRB.TransferIn(row);
        //            //case "PRO": BUS.MMS_PROGRAMMEControl ctrPRO = new MMS_PROGRAMMEControl(); return ctrPRO.TransferIn(row);
        //            //case "MMS": BUS.MMS_MEMBERControl ctrMMS = new MMS_MEMBERControl(); return ctrMMS.TransferIn(row);
        //            //case "TBD": BUS.TBDControl ctrTBD = new TBDControl(); return ctrTBD.TransferIn(row);
        //            //case "TIM": BUS.TIMControl ctrTIM = new TIMControl(); return ctrTIM.TransferIn(row);
        //            //case "ZMS": BUS.ZMSControl ctrZMS = new ZMSControl(); return ctrZMS.TransferIn(row);

        //            case "RTD": BUS.RTDControl ctrRTD = new RTDControl(); return ctrRTD.TransferIn(row);
        //            case "RPT":
        //            case "COR":
        //            case "DIS":
        //            case "TOW":
        //            case "TG1":
        //            case "TG2":
        //            case "LOK":
        //            case "TG3":
        //            case "TG4": BUS.LIST_LOOKUPControl ctrTG4 = new LIST_LOOKUPControl(); return ctrTG4.TransferIn(row);
        //            //case "TDS": BUS.TDSControl ctrTDS = new TDSControl(); return ctrTDS.TransferIn(row);
        //            case "QDADD": BUS.LIST_QD_SCHEMAControl ctrSchema = new LIST_QD_SCHEMAControl(); return ctrSchema.TransferIn(row);

        //        }
        //        return "";
        //    }
        //    catch (Exception ex)
        //    {
        //        return ex.Message;
        //    }
        //}

        public static string Update(DataRow row, string tableName, DTO.PODInfo PODInfo)
        {
            try
            {
                switch (tableName)
                {
                    #region Common
                    case "Vendor": BUS.VENDORControl ctrVendor = new VENDORControl(); return ctrVendor.Update_VENDOR(new DTO.VENDORInfo(row));
                    case "Budget":
                        BUS.BudgetsControl ctrBudget = new BudgetsControl();
                        if (row["BudgetCode"].ToString() == "")
                            return "Could not save blank Budget, please check!";
                        if (!ctrBudget.IsExist_Budgets(row["BudgetCode"].ToString(), int.Parse(row["Year"].ToString())))
                            return "This Budget is not existed, please check!";
                        if (ctrBudget.IsExist_GroupOtherShared(row["BudgetCode"].ToString(), int.Parse(row["Year"].ToString()), row["Group"].ToString(), row["isShared"] + "" == "" ? (Int16)0 : (Int16)row["isShared"]))
                            return "There are shared budget and none shared budget in budget group " + row["Group"].ToString() + ", please check!";

                        sErr = ctrBudget.Update_Budgets(new DTO.BudgetsInfo(row));
                        sErr += ctrBudget.Update_Shared(new DTO.BudgetsInfo(row));
                        return sErr;

                    case "Budget1":
                        BUS.BudgetsControl ctrBudget1 = new BudgetsControl();
                        if (row["BudgetCode"].ToString() == "")
                            return "Could not save blank Budget, please check!";
                        if (!ctrBudget1.IsExist_Budgets1(row["BudgetCode"].ToString(), int.Parse(row["Year"].ToString())))
                            return "This Budget is not existed, please check!";
                        if (ctrBudget1.IsExist_GroupOtherShared1(row["BudgetCode"].ToString(), int.Parse(row["Year"].ToString()), row["Group"].ToString(), row["isShared"] + "" == "" ? (Int16)0 : (Int16)row["isShared"]))
                            return "There are shared budget and none shared budget in budget group " + row["Group"].ToString() + ", please check!";
                        sErr = ctrBudget1.Update_Budgets1(new DTO.BudgetsInfo(row));
                        sErr += ctrBudget1.Update_Shared(new DTO.BudgetsInfo(row));
                        return sErr;
                    case "ItemRecord": BUS.TVC_RFITMControl ctrRFITM = new TVC_RFITMControl(PODInfo.Business_ID);
                        return ctrRFITM.Update(PODInfo.Database, new DTO.TVC_RFITMInfo(row));

                    case "MMT": BUS.TVC_MVPControl ctrMMT = new TVC_MVPControl(PODInfo.Business_ID); return ctrMMT.Update(PODInfo.Database, new DTO.TVC_MVPInfo(row));
                    case "Location":
                    case "BinNo":
                    case "Supplier":
                    case "Categories":
                    case "LOOKUP_ACCNT":
                    case "UND":
                    case "UCT":
                    case "Parameter": BUS.TVC_LOOKUPControl ctrParameter = new TVC_LOOKUPControl(PODInfo.Business_ID); return ctrParameter.Update(PODInfo.Database, new DTO.TVC_LOOKUPInfo(row));
                    case "RUS": BUS.RuleProfileControl ctrRUS = new RuleProfileControl(PODInfo.Business_ID); return ctrRUS.Update(PODInfo.Database, new DTO.RuleProfileInfo(row));

                    case "DB": BUS.DBAControl ctrDB = new DBAControl(); return ctrDB.Update(new DTO.DBAInfo(row));
                    case "POD": BUS.PODControl ctrPOD = new PODControl(); return ctrPOD.Update(new DTO.PODInfo(row));
                    case "POG": BUS.POGControl ctrPOG = new POGControl(); return ctrPOG.Update(new DTO.POGInfo(row));
                    case "POP": BUS.POPControl ctrPOP = new POPControl(); return ctrPOP.Update(new DTO.POPInfo(row));
                    case "SNS": BUS.SNSControl ctrSNS = new SNSControl(); return ctrSNS.Update(new DTO.SNSInfo(row));
                    case "DA": BUS.LIST_DAControl ctrDA = new LIST_DAControl(); return ctrDA.Update(new DTO.LIST_DAInfo(row));
                    case "MLC": BUS.MLCControl ctrMLC = new MLCControl(); return ctrMLC.Update(new DTO.MLCInfo(row));
                    #endregion

                    #region Reference
                    #endregion

                    #region Sale
                    case "SAL":
                    #endregion

                    #region Activity
                    #endregion

                    #region Incentive
                    #endregion

                    //case "POS": BUS.POSControl ctrPOS = new POSControl(); return ctrPOS.Update(new DTO.PODInfo(row));
                    case "RSA":
                    case "RQM":
                    case "RQY": BUS.LIST_QDControl ctrRQY = new LIST_QDControl(); return ctrRQY.Update_LIST_QD(new DTO.LIST_QDInfo(row));
                    //case "PRD": BUS.PRDControl ctrPRD = new PRDControl(); return ctrPRD.Update(new DTO.PODInfo(row));
                    case "PRB": BUS.LIST_PRIControl ctrPRB = new LIST_PRIControl(); return ctrPRB.Update(new DTO.LIST_PRIInfo(row));
                    //case "PRO": BUS.MMS_PROGRAMMEControl ctrPRO = new MMS_PROGRAMMEControl(); return ctrPRO.Update(new DTO.PODInfo(row));
                    //case "MMS": BUS.MMS_MEMBERControl ctrMMS = new MMS_MEMBERControl(); return ctrMMS.Update(new DTO.PODInfo(row));
                    //case "TBD": BUS.TBDControl ctrTBD = new TBDControl(); return ctrTBD.Update(new DTO.PODInfo(row));
                    //case "TIM": BUS.TIMControl ctrTIM = new TIMControl(); return ctrTIM.Update(new DTO.PODInfo(row));
                    //case "ZMS": BUS.ZMSControl ctrZMS = new ZMSControl(); return ctrZMS.Update(new DTO.PODInfo(row));

                    case "RTD": BUS.RTDControl ctrRTD = new RTDControl(); return ctrRTD.Update(new DTO.RTDInfo(row));
                    case "RPT":
                    case "COR":
                    case "DIS":
                    case "TOW":
                    case "TG1":
                    case "TG2":
                    case "LOK":
                    case "TG3":
                    case "TG4": BUS.LIST_LOOKUPControl ctrTG4 = new LIST_LOOKUPControl(); return ctrTG4.Update(new DTO.LIST_LOOKUPInfo(row));
                    //case "TDS": BUS.TDSControl ctrTDS = new TDSControl(); return ctrTDS.Update(new DTO.PODInfo(row));
                    case "QDADD": BUS.LIST_QD_SCHEMAControl ctrSchema = new LIST_QD_SCHEMAControl(); return ctrSchema.Update(new DTO.LIST_QD_SCHEMAInfo(row));
                    case "DPT": BUS.DepartmentsControl cDPTCtr = new DepartmentsControl(); return cDPTCtr.Update(new DTO.DepartmentsInfo(row));

                }
                return "";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        public static string Insert(DataRow row, string tableName, DTO.PODInfo PODInfo)
        {
            try
            {
                switch (tableName)
                {
                    case "Vendor": BUS.VENDORControl ctrVendor = new VENDORControl();
                        ctrVendor.Add_VENDOR(new DTO.VENDORInfo(row), ref sErr);
                        return sErr;
                    case "Budget": BUS.BudgetsControl ctrBudget = new BudgetsControl();
                        if (row["BudgetCode"].ToString() == "")
                            return "Could not save blank Budget, please check!";
                        if (ctrBudget.IsExist_Budgets(row["BudgetCode"].ToString(), int.Parse(row["Year"].ToString())))
                            return "This Budget is already existed, please check!";
                        if (ctrBudget.IsExist_GroupOtherShared(row["BudgetCode"].ToString(), int.Parse(row["Year"].ToString()), row["Group"].ToString(), row["isShared"] + "" == "" ? (Int16)0 : (Int16)row["isShared"]))
                            return "There are shared budget and none shared budget in budget group " + row["Group"].ToString() + ", please check!";

                        ctrBudget.Add_Budgets(new DTO.BudgetsInfo(row), ref sErr);
                        sErr += ctrBudget.Update_Shared(new DTO.BudgetsInfo(row));
                        return sErr;
                    case "Budget1": BUS.BudgetsControl ctrBudget1 = new BudgetsControl();
                        if (row["BudgetCode"].ToString() == "")
                            return "Could not save blank Budget, please check!";
                        if (ctrBudget1.IsExist_Budgets1(row["BudgetCode"].ToString(), int.Parse(row["Year"].ToString())))
                            return "This Budget is already existed, please check!";
                        if (ctrBudget1.IsExist_GroupOtherShared1(row["BudgetCode"].ToString(), int.Parse(row["Year"].ToString()), row["Group"].ToString(), row["isShared"] + "" == "" ? (Int16)0 : (Int16)row["isShared"]))
                            return "There are shared budget and none shared budget in budget group " + row["Group"].ToString() + ", please check!";

                        ctrBudget1.Add_Budgets1(new DTO.BudgetsInfo(row), ref sErr);
                        sErr += ctrBudget1.Update_Shared(new DTO.BudgetsInfo(row));
                        return sErr;
                    case "ItemRecord": BUS.TVC_RFITMControl ctrRFITM = new TVC_RFITMControl(PODInfo.Business_ID);
                        ctrRFITM.Add(PODInfo.Database, new DTO.TVC_RFITMInfo(row), ref sErr); return sErr;


                    case "MMT": BUS.TVC_MVPControl ctrMMT = new TVC_MVPControl(PODInfo.Business_ID);
                        ctrMMT.Add(PODInfo.Database, new DTO.TVC_MVPInfo(row), ref sErr); return sErr;
                    case "Location":
                    case "BinNo":
                    case "Supplier":
                    case "Categories":
                    case "LOOKUP_ACCNT":
                    case "UND":
                    case "UCT":
                    case "Parameter": BUS.TVC_LOOKUPControl ctrParameter = new TVC_LOOKUPControl(PODInfo.Business_ID);
                        ctrParameter.Add(PODInfo.Database, new DTO.TVC_LOOKUPInfo(row), ref sErr); return sErr;
                    case "RUS": BUS.RuleProfileControl ctrRUS = new RuleProfileControl(PODInfo.Business_ID);
                        ctrRUS.Add(PODInfo.Database, new DTO.RuleProfileInfo(row), ref sErr); return sErr;

                }
                return "";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        public static Object ValidatedDataTransferIn(Object obj, string tableName)
        {
            if (obj is DataTable)
            {
                DataTable dt = (DataTable)obj;
                DataTable dtStruct = (DataTable)GetTableStruct(tableName);
                DataColumn colValidated = new DataColumn("tmp_Validated", typeof(bool)) { Caption = "Valid" };
                dt.Columns.Add(colValidated);
                foreach (DataRow row in dt.Rows)
                {
                    bool flag = true;
                    foreach (DataColumn col in dtStruct.Columns)
                    {
                        if (flag)
                            switch (col.DataType.ToString())
                            {
                                case "System.Decimal":
                                    Decimal result;
                                    flag = Decimal.TryParse(row[col.ColumnName].ToString(), out result);
                                    break;
                                case "System.Int16":
                                    Int16 resultInt16;
                                    flag = Int16.TryParse(row[col.ColumnName].ToString(), out resultInt16);
                                    break;
                                case "System.Int32":
                                    Int32 resultInt32;
                                    flag = Int32.TryParse(row[col.ColumnName].ToString(), out resultInt32);
                                    break;
                                case "System.Int64":
                                    Int64 resultInt64;
                                    flag = Int64.TryParse(row[col.ColumnName].ToString(), out resultInt64);
                                    break;
                                case "System.Boolean":
                                    Boolean resultBoolean;
                                    flag = Boolean.TryParse(row[col.ColumnName].ToString(), out resultBoolean);
                                    break;
                            }
                        else
                            break;
                    }
                    row["tmp_Validated"] = flag;
                }
                return dt;
            }
            else if (obj is DataSet)
            {
                DataSet dset = (DataSet)obj;
                DataSet dsStruct = (DataSet)GetTableStruct(tableName);
                foreach (DataTable dt in dset.Tables)
                {
                    DataTable dtStruct = dsStruct.Tables[dt.TableName];
                    DataColumn colValidated = new DataColumn("tmp_Validated", typeof(bool)) { Caption = "Valid" };
                    if (!dt.Columns.Contains("tmp_Validated")) dt.Columns.Add(colValidated);
                    foreach (DataRow row in dt.Rows)
                    {
                        bool flag = true;
                        foreach (DataColumn col in dtStruct.Columns)
                        {
                            if (flag)
                                switch (col.DataType.ToString())
                                {
                                    case "System.Decimal":
                                        Decimal result;
                                        flag = Decimal.TryParse(row[col.ColumnName].ToString(), out result);
                                        break;
                                    case "System.Int16":
                                        Int16 resultInt16;
                                        flag = Int16.TryParse(row[col.ColumnName].ToString(), out resultInt16);
                                        break;
                                    case "System.Int32":
                                        Int32 resultInt32;
                                        flag = Int32.TryParse(row[col.ColumnName].ToString(), out resultInt32);
                                        break;
                                    case "System.Int64":
                                        Int64 resultInt64;
                                        flag = Int64.TryParse(row[col.ColumnName].ToString(), out resultInt64);
                                        break;
                                    case "System.Boolean":
                                        Boolean resultBoolean;
                                        flag = Boolean.TryParse(row[col.ColumnName].ToString(), out resultBoolean);
                                        break;
                                }
                            else
                                break;
                        }
                        row["tmp_Validated"] = flag;
                    }
                }
                return dset;
            }
            return null;
        }

        public static int ConvertDateStringToIn(string date, String dtb)
        {
            DBAControl dba_ctr = new DBAControl();
            DTO.DBAInfo dba_info = dba_ctr.Get(dtb, ref sErr);
            try
            {
                string[] arr = date.Split('/');
                if (dba_info.DATE_FORMAT == "B")
                {
                    int year = Convert.ToInt32(arr[2]);
                    int month = Convert.ToInt32(arr[1]);
                    int day = Convert.ToInt32(arr[0]);
                    return year * 10000 + month * 100 + day;
                }
                else if (dba_info.DATE_FORMAT == "A")
                {
                    int year = Convert.ToInt32(arr[2]);
                    int month = Convert.ToInt32(arr[0]);
                    int day = Convert.ToInt32(arr[1]);
                    return year * 10000 + month * 100 + day;
                }
                else //if (dba_info.DATE_FORMAT == "B")
                {
                    int year = Convert.ToInt32(arr[0]);
                    int month = Convert.ToInt32(arr[1]);
                    int day = Convert.ToInt32(arr[2]);
                    return year * 10000 + month * 100 + day;
                }
            }
            catch { }

            return 19000101;
        }
        public static bool TestConnection(string Server, string Username, string Pass, string Database)
        {
            string connectString = CreateConnectString(Server, Username, Pass, Database);
            return Connection.TestConnect(connectString);
        }

        public static DataTable GetDataBases(string Server, string UserName, string Pass)
        {
            return Connection.GetDataBases(Server, UserName, Pass);
        }

        //public static object executeScalar(string sqlString, string connectString)
        //{
        //    return Connection.executeScalar(sqlString, connectString);
        //}
        //public DataTable executeSelectQuery(string sqlString, string strConnection)
        //{
        //    return _conn.executeSelectQuery(sqlString, strConnection);
        //}

        public static bool IsNumber(string text)
        {
            bool res = true;
            try
            {
                if (!string.IsNullOrEmpty(text) && ((text.Length != 1) || (text != "-")))
                {
                    Decimal d = decimal.Parse(text, CultureInfo.CurrentCulture);
                }
            }
            catch
            {
                res = false;
            }
            return res;
        }
        public static string CreateConnectString(string Server, string Username, string Pass, string Database)
        {
            string connectString = "Data Source=[SERVER];Initial Catalog=[DATABASE]; uid=[USERNAME];pwd=[PASSWORD];Integrated Security=False;Connect Timeout=500";
            connectString = connectString.Replace("[SERVER]", Server).Replace("[DATABASE]", Database).Replace("[USERNAME]", Username).Replace("[PASSWORD]", Pass);

            return connectString;
        }
        public static string ToRoman(int arabic)
        {
            //string result = "";
            //int[] arabic = new int[] { 1000, 900, 500, 400, 100, 90, 50, 40, 10, 9, 5, 4, 1 };
            //string[] roman = new string[] { "M", "CM", "D", "CD", "C", "XC", "L", "XL", "X", "IX", "V", "IV", "I" };
            //int i = 0;
            //while (n >= arabic[i])
            //{
            //    n = n - arabic[i];
            //    result = result + roman[i];

            //    i = i + 1;
            //}
            //return result;
            /* Arabic Roman relation
            * 1000 = M
            * 900 = CM
            * 500 = D
            *400 = CD
            *100 = C
            *90 = XC
            *50 = L
            *40 = XL
            *10 = L
            *9 = IX
            *5 = V
            *4 = IV
            *1 = 1
            */
            string result = "";
            for (int i = 0; i < arabic; i++)
            {
                while (arabic >= 1000)
                {//check for thousands place

                    result = result + "M";
                    arabic = arabic - 1000;
                }
                while (arabic >= 900)
                {
                    //check for nine hundred place
                    result = result + "CM";
                    arabic = arabic - 900;
                }
                while (arabic >= 500)
                {
                    //check for five hundred place
                    result = result + "D";
                    arabic = arabic - 500;
                }
                while (arabic >= 400)
                {
                    //check for four hundred place
                    result = result + "CD";
                    arabic = arabic - 400;
                }
                while (arabic >= 100)
                {
                    //check for one hundred place
                    result = result + "C";
                    arabic = arabic - 100;
                }
                while (arabic >= 90)
                {
                    //check for ninety place
                    result = result + "XC";
                    arabic = arabic - 90;
                }
                while (arabic >= 50)
                {
                    //check for fifty place
                    result = result + "L";
                    arabic = arabic - 50;
                }
                while (arabic >= 40)
                {
                    // check for forty place
                    result = result + "XL";
                    arabic = arabic - 40;
                }

                while (arabic >= 10)
                {
                    // check for tenth place
                    result = result + "X";
                    arabic = arabic - 10;
                }
                while (arabic >= 9)
                {
                    //check for nineth place
                    result = result + "IX";
                    arabic = arabic - 9;
                }
                while (arabic >= 5)
                {
                    //check for fifth place
                    result = result + "V";
                    arabic = arabic - 5;
                }
                while (arabic >= 4)
                {
                    //check for fourth place
                    result = result + "IV";
                    arabic = arabic - 4;
                }
                while (arabic >= 1)
                {
                    //check for first place
                    result = result + "I";
                    arabic = arabic - 1;
                }
            }
            return result;
        }
        //public DataTable ExeSelectQuery(string query)
        //{
        //    return _conn.executeSelect(query);
        //}

        public static string GetConnection()
        {
            return Connection.ConnectionString;
        }
        public static void SetConnection(string val)
        {
            Connection.ConnectionString = val;
        }
        public static void AddLog(string type, string path, string message)
        {
            string erroFile = String.Format("{0}\\{1}.log", path, type);
            //System.IO.StreamWriter sw = new System.IO.StreamWriter(erroFile);
            if (!File.Exists(erroFile))
            {
                using (StreamWriter swt = File.CreateText(erroFile))
                {
                    swt.WriteLine(message);
                    swt.Close();
                }

            }
            else
            {
                FileStream file = new FileStream(erroFile, FileMode.Append);
                StreamWriter sw = new StreamWriter(file);
                sw.WriteLine(message);
                sw.Close();
                file.Close();
            }
        }
        public static string RemoveAttribute(String input, string attribute)
        {
            int indexS = input.IndexOf(attribute, 0);
            if (indexS != -1)
            {
                int indexE = input.IndexOf(";", indexS + attribute.Length);
                if (indexE == -1 || indexE == input.Length - 1)
                    return input.Substring(0, indexS);
                return input.Substring(0, indexS) + input.Substring(indexE + 1);
            }
            else
                return input;
        }
        public static string HiddenAttribute(String input, string attribute)
        {
            int indexS = input.IndexOf(attribute, 0);
            if (indexS != -1)
            {
                int indexE = input.IndexOf(";", indexS + attribute.Length);
                if (indexE == -1 || indexE == input.Length - 1)
                    return String.Format("{0}=******;{1}", attribute, input.Substring(0, indexS));
                return String.Format("{0}{1}=*****;{2}", input.Substring(0, indexS), attribute, input.Substring(indexE + 1));
            }
            else
                return String.Format("{0}=*****;{1}", attribute, input);
        }

        public DataTable Getdata(string ConnecImport, string Query, ref string sErr)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = null;
            SqlDataReader reader = null;
            try
            {
                // Mở kết nối
                conn = new SqlConnection(ConnecImport);
                conn.Open();
                //Thực thi query
                SqlCommand cmd = new SqlCommand(Query, conn);
                reader = cmd.ExecuteReader();
                dt.Load(reader);
            }
            catch (Exception e)
            {
                sErr = "Error: " + e;
            }
            finally
            {
                //Ngắt kết nối
                if (reader != null) reader.Close();
                conn.Close();
            }
            return dt;
        }
        public void executeQuery(string ConnecImport, string Query, ref string sErr)
        {

            // Mở kết nối
            using (SqlConnection conn = new SqlConnection(ConnecImport))
            {
                try
                {
                    conn.Open();
                    //Thực thi query
                    using (DataSet ds = new DataSet())
                    {
                        SqlCommand cmd = new SqlCommand(Query, conn);
                        cmd.ExecuteNonQuery();
                        //adapter = new SqlDataAdapter(Query, conn);
                        //adapter.Fill(ds);
                    }
                }
                catch (Exception e)
                {
                    sErr = "Error: " + e;
                }
                finally
                {
                    conn.Close();

                }

            }



        }
    }
}
